from sqlalchemy import or_, exc, and_
from sqlalchemy.orm import Session

from model.ParamModel import SysDictDetailModel
from model.TeachingModel import EmployeeModel
from schema.QuerySchema import QueryBase, EmployeeQuery
from schema.TeachingSchema import EmployeeInfo
from util.ErrorCode import ErrorCode
from util.LogHelper import Log


def add_employee(employeeInfo: EmployeeInfo, db: Session):
    employeeModel = db.query(EmployeeModel).filter(EmployeeModel.phone == employeeInfo.phone).one_or_none()
    if employeeModel is None:
        employeeModel = EmployeeModel(phone=employeeInfo.phone, email=employeeInfo.email, name=employeeInfo.name,
                                      sex=employeeInfo.sex,
                                      port=employeeInfo.port, status=employeeInfo.status,
                                      entry_date=employeeInfo.entry_date,
                                      leave_date=employeeInfo.leave_date)

        try:
            db.add(employeeModel)
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            db.rollback()
            Log.getlogger().error(f'add_employee failed:{employeeInfo} message: {e}')
            return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        return {'code': ErrorCode.EMPLOYEE_ALREADY_EXITS.value, 'message': 'employee phone already exists!'}


def mod_employee(employeeInfo: EmployeeInfo, db: Session):
    employeeModel = db.query(EmployeeModel).filter(EmployeeModel.id == employeeInfo.id).one_or_none()
    if employeeModel is not None:
        if employeeInfo.email is not None:
            employeeModel.email = employeeInfo.email if len(employeeInfo.email) != 0 else employeeModel.email
        if employeeInfo.name is not None:
            employeeModel.name = employeeInfo.name if len(employeeInfo.name) != 0 else employeeModel.name
        if employeeInfo.sex != 0:
            employeeModel.sex = employeeInfo.sex
        if employeeInfo.port != 0:
            employeeModel.port = employeeInfo.port
        if employeeInfo.status != 0:
            employeeModel.status = employeeInfo.status
        if employeeInfo.entry_date is not None:
            employeeModel.entry_date = employeeInfo.entry_date if len(
                employeeInfo.entry_date) != 0 else employeeModel.entry_date
        if employeeInfo.leave_date is not None:
            employeeModel.leave_date = employeeInfo.leave_date if len(
                employeeInfo.leave_date) != 0 else employeeModel.leave_date

        try:
            db.commit()
            return employeeModel
            # return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            Log.getlogger().error(f'mod_employee failed:{employeeInfo} message: {e}')
            return None
            # return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(f'id:{employeeInfo.id} is not exists!')
        return None
        # return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


def query_employee_by_page(query_base: QueryBase, db: Session):
    try:
        total_count = db.query(EmployeeModel).count()
        total_pages = (total_count + query_base.limit - 1) // query_base.limit
        result = db.query(EmployeeModel).offset((query_base.skip - 1) * query_base.limit).limit(query_base.limit).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count,
                'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_employee_by_page failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_employee_by_id(employee_id: int, db: Session):
    try:
        employeeModel = db.query(EmployeeModel).filter(EmployeeModel.id == employee_id).one_or_none()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': employeeModel}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_employee_by_id failed! id: {employee_id}  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


# 通过phone、name、port查询user
def query_employee_by_field(employeeQuery: EmployeeQuery, db: Session):
    try:
        if employeeQuery.phone is not None and len(employeeQuery.phone) == 0:
            employeeQuery.phone = None
        if employeeQuery.name is not None and len(employeeQuery.name) == 0:
            employeeQuery.name = None
        total_count = db.query(EmployeeModel).filter(
            and_(or_(EmployeeModel.phone == employeeQuery.phone, employeeQuery.phone is None),
                 or_(EmployeeModel.name == employeeQuery.name, employeeQuery.name is None),
                 or_(EmployeeModel.port == employeeQuery.port, employeeQuery.port == 0))).count()
        total_pages = (total_count + employeeQuery.query_base.limit - 1) // employeeQuery.query_base.limit

        result = db.query(EmployeeModel).filter(
            and_(or_(EmployeeModel.phone == employeeQuery.phone, employeeQuery.phone is None),
                 or_(EmployeeModel.name == employeeQuery.name, employeeQuery.name is None),
                 or_(EmployeeModel.port == employeeQuery.port, employeeQuery.port == 0))).offset(
            (employeeQuery.query_base.skip - 1) * employeeQuery.query_base.limit).limit(
            employeeQuery.query_base.limit).all()

        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count,
                'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_employee_by_field failed! {employeeQuery}  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_employee_by_port_id(port_id: int, db: Session):
    try:
        result = db.query(EmployeeModel).filter(or_(EmployeeModel.port == port_id, port_id == 0)).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_employee_by_port_id failed! port_id: {port_id}  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_employee_all_group_by_port(db: Session):
    try:
        result = db.query(SysDictDetailModel.sys_dict_item_id, SysDictDetailModel.sys_dict_item_name, EmployeeModel.email, EmployeeModel.name).join(EmployeeModel, EmployeeModel.port == SysDictDetailModel.sys_dict_item_id).filter(SysDictDetailModel.sys_dict_id == 1001).order_by(EmployeeModel.port).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_employee_all_group_by_port failed!  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
